package br.com.hmm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.hmm.entidade.Formula;

public class FormulaDAO {
	
	private Connection conn = null;
	private static final String INSERIR_FORMULA = "INSERT INTO FORMULA (FOR_ID, FOR_NOME, FOR_TIPO, FOR_DESCRICAO) VALUES(NULL, ?, ?, ?)";
	private static final String ALTERAR_FORMULA = "UPDATE FORMULA SET FOR_NOME = ?, FOR_TIPO = ?, FOR_DESCRICAO = ? WHERE FOR_ID = ?";
	private static final String DELETAR_FORMULA = "DELETE FROM FORMULA WHERE FOR_ID = ?";
	private static final String FIND_BY_ID      = "SELECT FOR_NOME, FOR_TIPO, FOR_DESCRICAO FROM FORMULA WHERE FOR_ID = ?";
	private static final String FIND_ALL		= "SELECT FOR_ID, FOR_NOME, FOR_TIPO, FOR_DESCRICAO FROM FORMULA";
	
	
	/**
	 * Construtor da classe FormulaDAO
	 */
	public FormulaDAO(){
		
	}
	
	/**
	 * Insere uma fórmula no banco de dados
	 * @param formula
	 * @return Boolean
	 * @throws Exception 
	 */
	public boolean inserir(Formula formula) throws Exception{
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(INSERIR_FORMULA);
			stmt.setString(1, formula.getNome());
			stmt.setString(2, formula.getTipo());
			stmt.setString(3, formula.getDescricao());
			if(stmt.executeUpdate() > 0){
				return true;
			}
	}catch(Exception e){
		throw new Exception(e.getMessage());
	}finally{
		DAOUtil.close(this.conn, stmt);
	}
	return false;
   }
	
	/**
	 * Atualiza os dados
	 * de uma fórmula no 
	 * banco de dados
	 * @param formula
	 * @return boolean
	 * @throws Exception 
	 */
	public boolean update(Formula formula) throws Exception{
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(ALTERAR_FORMULA);
			stmt.setString(1, formula.getNome());
			stmt.setString(2, formula.getTipo());
			stmt.setString(3, formula.getDescricao());
			stmt.setInt(4, formula.getId());
			if(stmt.executeUpdate() > 0){
				return true;
			}
	}catch(Exception e){
		throw new Exception(e.getMessage());
	}finally{
		DAOUtil.close(this.conn, stmt);
	}
	return false;
   
	}
	
	/**
	 * Remove os dados de uma fórmula
	 * no banco de dados.
	 * @param formula
	 * @return boolean
	 * @throws Exception 
	 */
	public boolean delete(Formula formula) throws Exception{
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(DELETAR_FORMULA);
			stmt.setInt(1, formula.getId());
			if(stmt.executeUpdate() > 0 ){
				return true;
			}
		}catch(Exception e){
			throw new Exception(e.getMessage());
		}finally{
			DAOUtil.close(this.conn, stmt);
		}
		return false;
	}
	
	/**
	 * Retorna os dados de uma 
	 * fórmula localizada pelo seu
	 * id
	 * @param formula
	 * @return Formula
	 * @throws Exception 
	 */
	public Formula findById(Formula formula) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(FIND_BY_ID);
			stmt.setInt(1, formula.getId());
			rs = stmt.executeQuery();
			if(rs.first() == true){
				Formula f = new Formula();
				f.setId(rs.getInt("FOR_ID"));
				f.setNome(rs.getString("FOR_NOME"));
				f.setTipo(rs.getString("FOR_TIPO"));
				f.setDescricao(rs.getString("FOR_DESCRICAO"));
				return f;
			}
			}catch(Exception e){
				throw new Exception(e.getMessage());
			}finally{
				DAOUtil.close(this.conn, stmt, rs);
			}
			
			return null;
	}
	
	/**
	 * Retorna uma lista
	 * contendo todas as 
	 * fórmulas cadastradas no banco.
	 * @return List<Formula>
	 * @throws Exception 
	 */
	public List<Formula> findAll() throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(FIND_ALL);
			rs = stmt.executeQuery();
			List<Formula> lf = new ArrayList<Formula>();
			while(rs.next()){
				Formula f = new Formula();
				f.setId(rs.getInt("FOR_ID"));
				f.setNome(rs.getString("FOR_NOME"));
				f.setTipo(rs.getString("FOR_TIPO"));
				f.setDescricao(rs.getString("FOR_DESCRICAO"));
				lf.add(f);
			}
			if(!lf.isEmpty()){
				return lf;
			}
	}catch(Exception e){
		throw new Exception(e.getMessage());
	}finally{
		DAOUtil.close(this.conn, stmt, rs);
		
	}
	
	return null;
	}
	
	

}
